# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql
from scrapy.exporters import JsonLinesItemExporter, JsonItemExporter

from csdnpersionalblog.my_exporters import ExcelItemExporter


class CsdnMysqlPipeline:
    def __init__(self, host, port, db, user, password):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST', '127.0.0.1'),
            port=crawler.settings.get('MYSQL_PORT', 3306),
            db=crawler.settings.get('MYSQL_DB'),
            user=crawler.settings.get('MYSQL_USER', 'root'),
            password=crawler.settings.get('MYSQL_PASSWORD', 'root')
        )

    def open_spider(self, spider):
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            db=self.db,
            user=self.user,
            password=self.password
        )
        self.cur = self.conn.cursor()

    def close_spider(self, spider):
        self.cur.close()
        self.conn.close()

    def process_item(self, item, spider):
        if 'unlie' not in item.fields:
            item['unlike'] = 0
        keys = item.keys()
        values = list(item.values())
        sql = 'insert into blog({}) values ({}) '.format(
            ','.join(keys),
            ','.join(['%s'] * len(values))
        )
        # sql = 'insert into blog(title, publish, approval, unlike, `comment`, collection) values (%s, %s, %s, %s, %s, %s)'
        self.cur.execute(sql, values)
        self.conn.commit()
        print(self.cur._last_executed)
        return item


class JSONPipeline:
    def __init__(self):
        self.fp = open("../../output/csdn.json", "wb")
        self.exporter = JsonItemExporter(self.fp, encoding='utf-8')

    def open_spider(self, spider):
        self.exporter.start_exporting()

    def process_item(self, item, spider):
        self.exporter.export_item(item)
        return item

    def close_spider(self, spider):
        self.exporter.finish_exporting()
        self.fp.close()


class ExcelPipeline:
    def __init__(self):
        self.fp = open("../../output/csdn.xls", "wb")
        self.exporter = ExcelItemExporter(self.fp, encoding='utf-8')

    def open_spider(self, spider):
        self.exporter.start_exporting()

    def process_item(self, item, spider):
        self.exporter.export_item(item)
        return item

    def close_spider(self, spider):
        self.exporter.finish_exporting()
        self.fp.close()
